# -*- coding: utf-8 -*-
# @Time    : 2021/8/2 19:35
# @Author  : 李吉凯
# @File    : excel_operate.py
from pprint import pprint

import pandas
import pandas as pd
import xlrd, os, xlwt
import collections

from BladeFerm.Lib.Common import logger
from openpyxl import load_workbook
from xlrd import open_workbook
from xlutils.copy import copy
import win32com.client as win32


#excel数据处理
class DealXlsElement():
    def __init__(self, sheet_name, path='../config/taskDeliver.xlsx'):
        self.sheet_name = sheet_name
        self.path = path
        self.data = None

    def setSheetName(self, sheet_name):
        self.sheet_name = sheet_name

    def setPath(self, path):
        self.path = path

    def readExcel(self):
        self.data = xlrd.open_workbook(self.path)
        logger.info(f"data: {self.data}")


    def dealxlselement(self, kind, column=0):
        #kind 1表示获取字典,2为获取二维列表，column表示列
        #字典形式
        InputElement = collections.OrderedDict()
        data = xlrd.open_workbook(self.path)
        #读取参数表
        try:
            table = data.sheet_by_name(self.sheet_name)
        except:
            table = data.sheet_by_index(0)
        # 通过索引顺序获取工作表
        if kind =='dict':
            for i in range(table.nrows):
                InputElement[table.cell(i, column).value] = table.cell(i, column+1).value
            return InputElement
        elif kind == 'list':
            rows = []
            for i in range(table.nrows):
                rows.append(table.row_values(i))
            return rows

    def style_excel(self,name, height,color=0, bond=False, bord=False, background=False,wrap=False,HORZ_CENTER=True):
        style = xlwt.XFStyle()  # 可以设置6个样式类，font,borders,aligment,pattern,protection,num_format_str
        # public.字体样式
        font = xlwt.Font()
        font.height = height  # 默认高度
        font.italic = False  # 默认不设置斜体,true为开启
        font.name = name  # 字体名字'Times New Roman'
        font.bold = bond
        font.colour_index =color  #0 = Black, public = White, 2 = Red, 3 = Green, 4 = Blue, 5 = Yellow, 6 = Magenta,  the list goes on
        style.font = font
        # 2.边框
        if bord == True:
            borders = xlwt.Borders()
            borders.left = borders.THIN
            borders.right = borders.THIN  # DASHED虚线，NO_LINE没有，THIN实线，也可写数字
            borders.top = borders.THIN
            borders.bottom = borders.THIN
            style.borders = borders
        # 3.aligment设置
        aligment = xlwt.Alignment()
        aligment.vert = aligment.VERT_CENTER  # 垂直居中
        if HORZ_CENTER==True:
            aligment.horz = aligment.HORZ_CENTER  # 水平居中
        if wrap==False:
            aligment.wrap = aligment.WRAP_AT_RIGHT  # 自动换行
        else:
            pass
        style.alignment = aligment
        # 4.背景色pattern
        if background == True:
            pattern = xlwt.Pattern()
            pattern.pattern = pattern.SOLID_PATTERN  # Create the Pattern
            pattern.pattern_fore_colour = 5  ## May be: 8 through 63. 0 = Black, public = White, 2 = Red, 3 = Green, 4 = Blue, 5 = Yellow, 6 = Magenta, 7 = Cyan, 16 = Maroon, 17 = Dark Green, 18 = Dark Blue, 19 = Dark Yellow , almost brown), 20 = Dark Magenta, 21 = Teal, 22 = Light Gray, 23 = Dark Gray
            style.pattern = pattern
        return style


class ExcelOfPandas:
    def __init__(self):
        self.df = None
        self.sheets = None
        self.data = {}

    def replace_nan(self, fill=""):
        self.df = self.df.fillna(fill)

    def readSheets(self, path, engine='openpyxl', auto_read=False):
        io = pd.ExcelFile(path, engine=engine)
        self.sheets = io.sheet_names
        logger.info(f'sheets: {self.sheets}')
        if auto_read:
            for sheet in self.sheets:
                df = self.readExcel(path, sheet_name=sheet, engine=engine)
                self.data[sheet] = df
        return self.sheets

    def readExcel(self, path, sheet_name=0, header=0, **kwargs):
        '''
        Funtion: 读取excel内容
        :param sheet_name: 1.不填，获取第一个sheet页内容；2.填写sheet页名称；3.填写sheet页的id；4.列表方式，获取多个sheet页内容
        :return: 
        '''
        self.df = pd.read_excel(path, sheet_name=sheet_name, header=header, **kwargs)
        return self.df

    def readCSV(self, path):
        self.df = pd.read_csv(path)

    def sub_None(self, df_value):
        return df_value.where(df_value.notnull(), None)

    def get_names(self, flag='columns'):
        return self.df.columns.tolist() if flag == 'columns' else self.df.index.tolist()
    
    def get_df_colnames(self, df, flag='columns'):
        return df.columns.tolist() if flag == 'columns' else df.index.tolist()
    
    def readCol(self, col_name):
        '''
        Funtion:读取指定的列
        :param row_id: 
        :return: 
        '''
        col_list = self.df[col_name]
        return col_list
    
    def readColOfDF(self, df, col_name):
        return df[col_name]

    @staticmethod
    def getDict(string: str) -> dict:
        res = {}
        for unit in string.split("\n"):
            kv = unit.split(":")
            if len(kv) == 1:
                kv = unit.split("：")
            if len(kv) == 2:
                key = kv[0].strip()
                value = kv[1].strip()
                res[key] = value
        return res

    def readRow(self, df, new_path):
        values = df.values
        cols = ["序号", "期望"]
        keys = []
        result = [{"序号": "参数说明", "期望": ""}]
        for line in values:
            name, input_str, exp_str = line[0], line[1], line[3]
            input_dict = ExcelOfPandas.getDict(input_str) if isinstance(input_str, str) else {}
            exp_str = exp_str.replace("\n", ";").replace(":", "=").replace("：", "=") if isinstance(exp_str, str) else ""
            temp = {"序号": name, "期望": exp_str}
            temp.update(input_dict)
            result.append(temp)
            keys.extend(list(input_dict.keys()))
        cols = cols + list(set(keys))
        new_df = pandas.DataFrame(result, columns=cols, index=None)
        logger.info(f"save new df: {new_path}")
        self.write_excel(new_path, new_df, sheet="测试数据", index=False, encoding='utf-8')
        return new_df

    def get_row_dict(self):
        result_dicts = []
        for pos in range(len(self.df)):
            row_values = self.df.iloc[pos].to_dict()
            result_dicts.append(row_values)
        return result_dicts

    def zip_dic(self, a_col, b_col):
        return self.df[[a_col, b_col]].set_index(a_col).to_dict()[b_col]

    def zip_df_dic(self, data_df, a_col, b_col):
        return data_df[[a_col, b_col]].set_index(a_col).to_dict()[b_col]
    
    def data_to_DF(self, data, index=None, col=None):
        pf = pd.DataFrame(data, index=index, columns=col)
        return pf

    def write_excel(self, filepath, data, sheet=None, **kwargs):
        data.to_excel(filepath, sheet_name=sheet, **kwargs)
        return filepath

    def modify_excel(self, filepath, data_df, sheet_name='sheet', startrow=0, startcol=0):
        '''
        Function: 往Excel中添加数据
        :param filepath: Excel文件路径
        :param data_df: pandas的数据结构，即将写入的数据行列
        :param sheet_name: 写入的sheet页名称
        :param startrow: 填写数据的位置，起始行号
        :param startcol: 填写数据的位置，起始列号
        :param engine: Excel读取的类型，xlsx用openpyxl，xls用xlrd // 此参数取消，根据文件后缀判断
        :return:
        '''
        file, ext = os.path.splitext(filepath)
        # 读取被写入的Excel工作簿
        if ext == '.xlsx':
            engine = 'openpyxl'
            book = load_workbook(filepath)
            # 建立写入对象
            write = pd.ExcelWriter(filepath, engine=engine)
            write.book = book
            write.sheets = {ws.title: ws for ws in book.worksheets}
            # 写入数据
            data_df.to_excel(write, sheet_name=sheet_name, header=False, index=False, startrow=startrow,
                             startcol=startcol)
        elif ext == '.xls':
            engine = 'xlwt'
            book = open_workbook(filepath)
            wb = copy(book)
            # 建立写入对象
            write = pd.ExcelWriter(filepath, engine=engine)
            write.book = wb
            write.sheets = {sheet_name: wb.get_sheet(0)}
            # 写入数据
            data_df.to_excel(write, sheet_name=sheet_name, header=False, index=False, startrow=startrow,
                             startcol=startcol)
        else:
            raise f"Excel读取的类型，xlsx用openpyxl，xls用xlrd"
        write.save()
        write.close()


def read_case_params(excelPath):
    fp, ext = os.path.splitext(excelPath)
    logger.info(f'fp: {fp}, ext: {ext}')
    if not os.path.exists(fp):
        os.makedirs(fp)
    EP = ExcelOfPandas()
    EP.readSheets(excelPath, engine='openpyxl')
    for sheet in EP.sheets:
        df = EP.readExcel(excelPath, sheet, engine='openpyxl')
        new_path = fp + "/" + sheet + ".xlsx"
        EP.readRow(df, new_path)



class ExcelOfWin32Com:
    def __init__(self):
        self.excel = win32.Dispatch('Excel.Application')
        self.wb = None
        self.ws = None

    def read_excel(self, filepath, sheet_name):
        self.wb = self.excel.Workbooks.open(filepath)
        self.ws = self.wb.Worksheets(sheet_name)
        return self.wb, self.ws

    def write_cell_value(self, pos, value):
        self.ws.Range(pos).value = value
        return self.ws

    def __titleToNumber(self, s):
        """
        :type s: str
        :rtype: int
        """
        result = 0
        for letter in s:
            result = result * 26 + (ord(letter) - ord('A')) + 1
        return result

    def filedata(self, pos, listData):
        if pos[1].isdigit():
            col, row = pos[0], pos[1:]
        else:
            col, row = pos[:2], pos[2:]
        start_col = self.__titleToNumber(col)
        start_row = int(row)
        if listData:
            if not isinstance(listData[0], list):
                for i in range(len(listData)):
                    self.ws.Cells(start_row, start_col+i).value = listData[i]
            else:
                j = 0
                for rowData in listData:
                    for i in range(len(rowData)):
                        self.ws.Cells(start_row + j, start_col + i).value = rowData[i]
                    j += 1
        return self.ws

    def sort_data(self, pos, targetCol):
        self.ws.Range(pos).Sort(Key1=self.ws.Range(targetCol), Order1=2, Orientation=1)

    def close_excel(self):
        if self.wb:
            self.wb.Save()
            self.wb.Close()


if __name__ == "__main__":
    a = DealXlsElement('模块任务').dealxlselement('list')
    pprint(a)
    for index, i in enumerate(a):
        print('index:', index)
        print('i', i)
